--1.한번에 시퀀스 다 날리기!!!
drop sequence history_no_seq;drop sequence workorder_no_seq;
drop sequence item_no_seq;drop sequence wip_no_seq;
--2.한번에 시퀀스 등록하기!!!
create sequence history_no_seq nocache;create sequence workorder_no_seq nocache;
create sequence item_no_seq nocache;create sequence wip_no_seq nocache;
--3.한번에 테이블 다 비우기!!!
delete history;delete wip;delete workorder;delete item_data;delete member;
delete next_step;delete deffect;
--4.한번에 필요한 테이블의 insert 한번에 넣기!!
insert into member values('master','12345');
insert into member values('manager','1234');
insert into deffect values(1,'비누화오염');
insert into deffect values(2,'경화오염');
insert into deffect values(3,'비누화불량');
insert into deffect values(4,'미경화');
insert into deffect values(5,'절단미스');
insert into deffect values(6,'재포장');
insert into next_step values(1,1,'비누화오염','폐기');
insert into next_step values(2,2,'경화오염','폐기');
insert into next_step values(3,3,'비누화불량','비누화_재가공');
insert into next_step values(4,4,'미경화','경화_재가공');
insert into next_step values(5,5,'절단미스','비누화_재가공');
insert into next_step values(6,6,'재포장','새로포장');
insert into item_data values(-1,'soap_base',0,sysdate,null,null);
insert into item_data values(-2,'oil',0,sysdate,null,null);
insert into item_data values(-3,'hardener',0,sysdate,null,null);
insert into item_data values(-4,'wrapper',0,sysdate,null,null);
---------------------------------------------------------------------------------------------
drop sequence history_no_seq;
create sequence history_no_seq nocache;

drop table history
select * from history
create table history(--히스토뤼
	history_no number primary key,
	history_date date not null,
	history_quantity number,
	history_status varchar2(10),
	item_no number,--쪼인
	serial_no number,
	workorder_no number,--쪼인
	workorder_quantity number,
	workorder_badquantity number,
	deffect_id number,
	member_id varchar2(100),--쪼인
	constraint item_no foreign key(item_no) references item_data,
	constraint wk2_no foreign key(workorder_no) references workorder,
	constraint def2_no foreign key(deffect_id) references deffect,
	constraint mem_no foreign key(member_id) references member
)

drop table member
select * from member
create table member(--멤바
	member_id varchar2(100) primary key,--멤버의 아이디로 경영자와 관리자 구분
	member_password varchar2(100) not null--패스워드로 보안
)

drop sequence workorder_no_seq;
create sequence workorder_no_seq nocache;

drop table workorder
select * from workorder
create table workorder(--지상명령
	workorder_no number primary key,--명령의고유번호(최신으로 찾아서 공정시작 들감)
	workorder_date date not null,--명령내린날짜
	workorder_quantity number not null,--목표수량
	workorder_badquantity number not null--목표불량량
)
delete workorder
select * from workorder
drop sequence item_no_seq;
create sequence item_no_seq nocache;

drop table item_data
select * from item_data
create table item_data(--원료
	item_no number primary key,--구입해서 들어온 원료의 고유번호
	item_id varchar2(100) not null,--원료(비누베이스,오일,염료,포장지) 구분
	item_oriquantity number not null,--구입해서 들어온 원료의 양
	item_date date not null,--구입한 날짜
	max_serial_no number,--하나의 공정에 들어간 마지막 시리얼 번호
	min_serial_no number--하나의 공정에 들어간 처음 시리얼 번호
)
select * from item_data

drop sequence wip_no_seq;
create sequence wip_no_seq nocache;

drop table wip
select * from wip
create table wip(--오류만 체크하는 테이블
	wip_no number primary key,--오류체크의 고유번호
	workorder_no number not null,--쪼인 | 지시 번호
	serial_no number not null,--제품 번호
	deffect_id number not null,--쪼인 | 오류 번호
	next_step_no number,--쪼인 | 다음 수행처리 번호
	wip_date date not null,--오류체크된 날짜
	constraint wk_no foreign key(workorder_no) references workorder,
	constraint def_id foreign key(deffect_id) references deffect,
	constraint nex_no foreign key(next_step_no) references next_step
)

drop table deffect
select * from deffect
create table deffect(--불량테이블
	deffect_id number primary key,--불량에 대한 고유의 번호
	deffect_name varchar2(100) not null--불량 내용
)

drop table next_step
select * from next_step
create table next_step(--다음공정이 재가공인지 폐기인지 수행하는 테이블
	next_step_no number primary key,
	deffect_id number not null,
	deffect_name varchar2(100) not null,
	next_process_id varchar2(100) not null,
	constraint def_id2 foreign key(deffect_id) references deffect
)



--마스터 로그인시 예)
insert into history values(history_no_seq.nextval,sysdate,null,null,
null,null,null,null,null,null,'master'); 
--매니저 로그인시 예)
insert into history values(history_no_seq.nextval,sysdate,null,null,
null,null,null,null,null,null,'manager');
--로그인 체크!! id와password를 주고, 맞으면 id를 반환하는 쿼리
select member_id from member where member_id='master' and member_password='369369'
--원료 구매 쿼리~ 입력값은 2개가 된다.                  이거랑,     이거!
insert into item_data values(item_no_seq.nextval,'soap_base',2000,sysdate,null,null);
insert into item_data values(item_no_seq.nextval,'oil',1000,sysdate,null,null);
insert into item_data values(item_no_seq.nextval,'hardener',1000,sysdate,null,null);
insert into item_data values(item_no_seq.nextval,'wrapper',4000,sysdate,null,null);
--운영자가 작업지시를 내리면 3가지쿼리가 실행된다.
insert into workorder values(workorder_no_seq.nextval,sysdate,300,10)--300과10은 만들 수량과 불량 예상 수량
select max(workorder_no) from workorder--제일 최신 지시사항 뽑아옴.				  1, 2 , 3에 값 들감!!
insert into history values(history_no_seq.nextval,sysdate,null,null,null,null,1,300,10,null,'manager')
--관리자가 작업지시를 볼 때,
select * from workorder where workorder_no=(
	select max(workorder_no) from workorder)
--공정시작 시, 원료가 충분한지 체크하려면 우선 해당 원료가 몇개인지 뽑아온다.
select sum(item_oriquantity) from item_data where item_id='비누베이스'
select sum(item_oriquantity) from item_data where item_id='오일'
select sum(item_oriquantity) from item_data where item_id='경화제'
select sum(item_oriquantity) from item_data where item_id='포장지'
--이때, 원료의 종류를 뽑는 쿼리로 원료를 list에 넣어 순서대로 넣어서 뽑는다.
select distinct(item_id) from item_data
--관리자가 명령을 내려서, 위의 확인 후 실제 !! 기계가 돌아가는 최대갯수(for문의 최대값)
select workorder_quantity from history where workorder_no=(
	select max(workorder_no) from history)
--공정 최초 or 에러직후 성공시, insert
insert into history values(history_no_seq.nextval,sysdate,1,'start',
null,1,1,null,null,null,'manager')
select * from history--테스트용 history
--성공 후 성공 1번째, 마지막 성공의 히스토리_양과 시리얼_넘버 가져오기
select history_quantity, serial_no from history where history_status='start'
	and history_no=(select max(history_no) from history)
--성공 후 성공 2번째, 업데이트하기
update history set history_quantity=2, serial_no=2 where history_no=(
	select max(history_no) from history)
select * from history--테스트용 history
--목표수량을 채우면 들어가는 쿼리
insert into history values(history_no_seq.nextval,sysdate,0,null,
null,400,1,null,null,null,'manager')
select * from history--테스트용 history
--에러 발생시,<<히스토리>>
--마지막 성공의 히스토리_양과 시리얼_넘버 가져오기(위와 동일한 쿼리 사용 중복제거!!)
select history_quantity, serial_no from history where history_status is null
	and history_no=(select max(history_no) from history)
--최초면(가져온 값이 null), 에러 insert(맨 뒤 바로 앞의 1은 error명 : 레어상태) 
insert into history values(history_no_seq.nextval,sysdate,1,'stop',
null,3,1,null,null,1,'manager')
select * from history--테스트용 history
--에러 발생시,<<WIP>>
--에러는 무조건 insert
insert into wip values(wip_no_seq.nextval,1,3,1,null,sysdate);
--에러다음 성공이 나면,(테스트용) 다시 체크를 하고
select history_quantity, serial_no from history where history_no=(
	select max(history_no) from history where history_status='start')
--시리얼만 +1해서 insert하고
insert into history values(history_no_seq.nextval,sysdate,1,'start',
null,3,1,null,null,null,'manager')
select * from history--테스트용 history
--그다음 성공이 되면 / 다시 체크를 해주고
select history_quantity, serial_no from history where history_no=(
	select max(history_no) from history where history_status='start')
--history_quan이랑 시리얼을 둘다 +1해줘서 또 update
update history set history_quantity=2, serial_no=4 where history_no=(
	select max(history_no) from history)
select * from history--테스트용 history

--마지막 수행이 stop인지 알아보는
select * from history where history_status='start' and history_no=
			(select max(history_no) from history where history_status is not null
			and workorder_no=2)

update history set history_date=sysdate, history_quantity=history_quantity+1,
	serial_no=serial_no+1 where history_status='start' and history_no=
	(select max(history_no) from history where history_status is not null
	and workorder_no=1)

insert into history values(history_no_seq.nextval,sysdate,1,'start',
		null,null,#workorder_no#,null,null,null,'manager')
	
select * from workorder where workorder_no=(
		select max(workorder_no) from workorder)

--목표수량
select sum(workorder_quantity) from (select to_char(history_date,'DD') as history_date,
	history_status,workorder_no,workorder_quantity from history) 
		where history_status='false' and workorder_no!=(select max(workorder_no) from history) 
			and history_date='29';
--목표불량수량
select sum(workorder_badquantity) from (select to_char(history_date,'DD') as history_date,
	history_status,workorder_no,workorder_badquantity from history) 
		where history_status='false' and workorder_no!=(select max(workorder_no) from history) 
			and history_date='29';
--성공수량
select sum(history_quantity) from (select history_no,to_char(history_date,'DD') as history_date,
	history_status,workorder_no,history_quantity from history) 
		where history_status='start' and workorder_no!=(select max(workorder_no) from history) 
			and history_date=29;
--실패수량		
select sum(history_quantity) from (select history_no,to_char(history_date,'DD') as history_date,
	history_status,workorder_no,history_quantity from history) 
		where history_status='stop' and workorder_no!=(select max(workorder_no) from history) 
			and history_date=29;
--오늘날짜받아오기
select to_char(sysdate,'DD') as history_date from dual

update history set history_date=sysdate, history_quantity=history_quantity+1
	where history_status='start' and history_no=
	(select max(history_no) from history where history_status is not null
	and workorder_no=6)
	
	select * from history

update history set workorder_quantity=#workorder_quantity#, workorder_badquantity=#workorder_badquantity# 
		where workorder_no=(select max(workorder_no) from workorder)
		
		
		select * from history

select h.history_date,h.serial_no,h.deffect_id,n.deffect_name,n.next_process_id 
	from history h, next_step n, (
	select history_no,to_char(history_date,'DD') as daydate from history) d 
		where h.deffect_id=n.deffect_id and h.history_no=d.history_no 
		and h.history_status='stop' and d.daydate='30' order by h.history_no desc




